Otázka č. 22 - Procedury, funkce, triggery, package a objekty

Procedury

• skupina SQL příkazů, které jsou uloženy pod jedním názvem a mohou být volány v rámci jiných SQL příkazů.

CREATE PROCEDURE GetCustomerName (IN customer_id INT)
BEGIN
    SELECT name FROM customers WHERE id = customer_id;
END

Typy parametrů

• IN
  • Procedura přijímá hodnotu od volajícího.
• OUT
  • Procedura vrací hodnotu volajícímu.
• INOUT
  • Procedura přijímá hodnotu od volajícího a může ji změnit.

Rozdíly mezi procedurou a funkcí

• Procedura nevrací hodnotu, zatímco funkce vrací hodnotu.
• procedura může vracet různý počet hodnot (0 až N)

Volání procedury

CALL GetCustomerName(1);

Funkce

• soubor SQL příkazů, který přijímá vstupní parametry, provádí operace a vrací výstup.

CREATE FUNCTION CalculateTotal (price DECIMAL(10,2), quantity INT)
RETURNS DECIMAL(10,2)
BEGIN
    RETURN price * quantity;
END

BiF x UDF

• BiF (Built-in Function): Standardní funkce poskytované DBMS.
• UDF (User-defined Function): Funkce vytvořená uživatelem.

Rozdíly mezi procedurou a funkcí

• Funkce vrací hodnotu, zatímco procedura nevrací hodnotu.
• Funkce vrací vždy jen jednu hodnotu.

Volání funkce

SELECT CalculateTotal(10.5, 5);

Triggery

• speciální typ uložené procedury, která je automaticky spuštěna při určité události na dané tabulce.

CREATE TRIGGER increment_animal AFTER INSERT ON animals FOR EACH ROW 
BEGIN
 IF NEW.name = 'Moose' THEN
  UPDATE animal_count SET animal_count.animals = animal_count.animals+100;
 ELSE 
  UPDATE animal_count SET animal_count.animals = animal_count.animals+1;
 END IF;
END;

Typy triggerů

• BEFORE
  • Trigger spuštěn před provedením události.
• AFTER
  • Trigger spuštěn po provedení události.

Multifunkční trigger

• Triggery mohou být definovány na tabulce tak, aby reagovaly na různé události (INSERT, UPDATE, DELETE)
• Trigger, který reaguje na více událostí

CREATE TRIGGER employee_audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
BEGIN
    IF INSERTING THEN
        INSERT INTO audit_table (action, employee_id, timestamp)
        VALUES ('INSERT', :NEW.employee_id, CURRENT_TIMESTAMP);
    ELSIF UPDATING THEN
        INSERT INTO audit_table (action, employee_id, timestamp)
        VALUES ('UPDATE', :NEW.employee_id, CURRENT_TIMESTAMP);
    ELSIF DELETING THEN
        INSERT INTO audit_table (action, employee_id, timestamp)
        VALUES ('DELETE', :OLD.employee_id, CURRENT_TIMESTAMP);
    END IF;
END;

Vzájemné volání triggerů

• Trigger může spustit další trigggery v závislosti na provedené akci.

CREATE TRIGGER after_insert_trigger AFTER INSERT ON emplloyees FOR EACH ROW 
BEGIN

    -- Pokud se vloží nový zaměstnanec, spusťte trigger pro aktualizaci počtu zaměstnanců
    CALL update_employee_count();
END;

Package

• seskupení procedur, funkcí a datových typů do logické jednotky, která může být sdílena mezi uživateli.

CREATE PACKAGE EmployeePackage AS
    PROCEDURE GetEmployeeName (emp_id INT);
    FUNCTION CalculateSalary (salary DECIMAL, bonus DECIMAL) RETURN DECIMAL;
END EmployeePackage;

CREATE PACKAGE BODY EmployeePackage AS
    PROCEDURE GetEmployeeName (emp_id INT) AS
    BEGIN
        SELECT name INTO emp_name FROM employees WHERE id = emp_id;
    END GetEmployeeName;

    FUNCTION CalculateSalary (salary DECIMAL, bonus DECIMAL) RETURN DECIMAL AS
    BEGIN
        RETURN salary + bonus;
    END CalculateSalary;

END EmployeePackage;

Rozdělení

• Hlavička, deklarace

CREATE PACKAGE EmployeePackage AS
    PROCEDURE GetEmployeeName (emp_id INT);
    FUNCTION CalculateSalary (salary DECIMAL, bonus DECIMAL) RETURN DECIMAL;
END EmployeePackage;

• Tělo, implementace

CREATE PACKAGE BODY EmployeePackage AS
    PROCEDURE GetEmployeeName (emp_id INT) AS
    BEGIN
        SELECT name INTO emp_name FROM employees WHERE id = emp_id;
    END GetEmployeeName;

    FUNCTION CalculateSalary (salary DECIMAL, bonus DECIMAL) RETURN DECIMAL AS
    BEGIN
        RETURN salary + bonus;
    END CalculateSalary;

END EmployeePackage;

Objekty

• Datové typy jsou datové typy, které umožňují ukládání a manipulaci s daty jako s objekty.

CREATE TYPE Address AS OBJECT (
    street VARCHAR2(50),
    city VARCHAR2(50),
    state VARCHAR2(2),
    zip VARCHAR2(10),

    MEMBER FUNCTION getFullAddress RETURN VARCHAR2
);

CREATE TYPE BODY Address AS
    MEMBER FUNCTION getFullAddress RETURN VARCHAR2 AS
    BEGIN
        RETURN street || ', ' || city || ', ' || state || ' ' || zip;
    END;

END;

Použití

• PostgreSQL
  • je známý svou podporou objektově-relačního modelu a nabízí robustní podporu pro objektové datové typy včetně vlastních typů, složitých typů a tabulkových typů.
• Oracle Database
  • Database podporuje objektové datové typy a umožňuje definovat a používat objektové typy, kolekce (seznamy, pole, asociativní pole), a další složité datové struktury.
• IBM Db2
  • nabízí podporu pro vytváření a používání uživatelsky definovaných datových typů (UDT) a vlastních typů (user-defined types), což umožňuje modelování komplexních datových struktur.
• Microsoft SQL Server
  • podporuje uživatelsky definované typy (User-defined types), které umožňují vytvářet objektové datové typy a definovat vlastní datové typy pro použití v databázovém schématu.
• MariaDB
  • Od verze 10.3 podporuje MariaDB objektové datové typy. MariaDB nabízí podporu pro objektové datové typy, což umožňuje vytvářet a používat vlastní složité datové struktury.